"""
原排期软件未升级，针对cgtw5的版本
排期软件数据库的数据读操作类


"""
import psycopg2
from .mz_date import MZ_Date

class Scheduler5Handler(object):

    def __init__(self):
        super(Scheduler5Handler, self).__init__()
        self.mz_date=MZ_Date()
        self.__project_map ={}
        self.__project_info ={}

    def _init_timeaxis_db(self):
        """
        初始化222排期数据库timeaxis连接
        :return: time_connect, time_cursor
        """
        try:
            time_connect = psycopg2.connect(database='postgres',
                                            user='timeaxis',
                                            password='timeaxis',
                                            host='192.168.1.222',
                                            port=5432)
            time_cursor = time_connect.cursor()
        except Exception as cgdb_err:
            print('cgdb_err:', cgdb_err)
            return None, None
        return time_connect, time_cursor


    def _init_taskprogress_db(self):
        """
        初始化222排期数据库taskprogress连接
        :return:
        """
        try:
            time_connect = psycopg2.connect(database='taskprogress',
                                          user='timeaxis',
                                          password='timeaxis',
                                          host='192.168.1.222',
                                          port=5432)
            time_cursor = time_connect.cursor()
        except Exception as _init_taskprogress_db_ERR:
            print('_init_taskprogress_db_ERR:',str(_init_taskprogress_db_ERR))
            return None, None
        return time_connect, time_cursor

    """
    ***********************************************
        r_project表操作
        r_project表同步cgtw的pulic/project表中信息
        字段:
        id: r_project表中id
        cg_id: cgtw数据库pulic中项目的id
        database: cgtw数据库中schemas名称 eg: proj_xxss
        entity_name: 项目拼音简称 eg : XXSS
        full_name:   项目中文名称 eg: 西夏死书
        color:   cgtw中项目的颜色
        statuts: 项目状态  eg: Active,Close
        eps:  项目中含有的集数 eg: {"eps_list":["01","02",...]}
        pipeline_list: 该项目的所有阶段 eg: {"pipeline_list":["mod_c","rig","efx","layout","cpt_c",...]}
        frame_rate:   该项目的帧数率  需要cgtw上设定

    ***********************************************
    """

    @property
    def project_map(self):
        """
        获取当前r_project表中项目信息
        :return: 返回以项目schemas名称为键，以[中文，简称]为值的字典，默认非制作项目都算作：'noproject':['非制作项目','NON']
        eg:{database:["无心法师","WXFS"],...}
        """
        if not self.__project_map:
            time_connect, time_cursor = self._init_timeaxis_db()
            sql="select database,entity_name,full_name from timeaxis.r_project"
            db_projectname_map={'noproject':['非制作项目','NON']}  #{database:["无心法师","WXFS"],...}
            try:
                time_cursor.execute(sql)
                result=time_cursor.fetchall()
                if result:
                    # print(result)
                    for i in result:
                        database,entity_name,full_name=i
                        db_projectname_map[database]=[full_name,entity_name]
            except Exception as get_r_project_ERR:
                print('get_r_project_ERR:',str(get_r_project_ERR))

            finally:
                time_connect.close()
            self.__project_map = db_projectname_map

        return self.__project_map



    @property
    def project_info(self):
        """
        获取r_project 所有项目信息
        :return: 返回以项目schemas为键，{data}为值的字典

        整体返回结果：{database:{id:xx,cg_id:xx,database:xx,entity_name:xx,full_name:xx,color:xx,status:xx,eps:xx,pipeline_list:xx,frame_rate},
        ...}
        """
        if not self.__project_info:
            time_connect, time_cursor = self._init_timeaxis_db()
            sql="select id,cg_id,database,entity_name,full_name,color,status,eps,pipeline_list,frame_rate from timeaxis.r_project"
            info_map={}  #{database:data,...}
            try:
                time_cursor.execute(sql)
                result=time_cursor.fetchall()
                if result:
                    # print(result)
                    for i in result:
                        pid,cg_id,database,entity_name,full_name,color,status,eps,pipeline_list,frame_rate=i
                        data={}
                        data['id']=pid
                        data['cg_id']=cg_id
                        data['database']=database
                        data['entity_name']=entity_name
                        data['full_name']=full_name
                        data['color']=color
                        data['status']=status
                        data['eps']=eps
                        data['pipeline_list']=pipeline_list
                        data['frame_rate']=frame_rate
                        info_map[database]=data
            except Exception as project_info_ERR:
                print('project_info_ERR:',str(project_info_ERR))
            finally:
                time_connect.close()
            self.__project_info = info_map
        return self.__project_info

    def project_color_map(self,project_info):
        """
        根据激活的项目，获取项目对应的颜色信息，用于wait状态的任务条.
        这个只需要初始化一次就行了，比较新项目创建不会很频繁
        :param:project_info
        :return:{database:{id:xx,cg_id:xx,database:xx,entity_name:xx,full_name:xx,color:xx,status:xx,eps:xx,pipeline_list:xx,frame_rate},
        ...}
        """
        _project_color_map ={}
        if project_info:
            for database,data in project_info.items():
                _project_color_map[database]=data['color']

        return _project_color_map


    """
    ***********************************************
        account表
        account表操作排期软件人员账号、名称、密码、部门以及排期软件UI配置记录
        字段:
        id: 
        account: 账号
        user_level: 权限等级
        custom_json: UI配置
        password: 密码
        user_name: 名称
        department: 部门
        
    ***********************************************
    """

    def account_info(self):
        """
        获取排期软件账户信息
        :return: 返回一个列表，每个元素是每人的信息
        """

        time_connect, time_cursor = self._init_timeaxis_db()
        sql="select id,account,user_level,custom_json,password,user_name,department from timeaxis.account"
        info_list=[]  #[data,data,...]
        try:
            time_cursor.execute(sql)
            result=time_cursor.fetchall()
            if result:
                for i in result:
                    pid,account,user_level,custom_json,password,user_name,department=i
                    data={}
                    data['id']=pid
                    data['account']=account
                    data['user_level']=user_level
                    data['custom_json']=custom_json
                    data['password']=password
                    data['user_name']=user_name
                    data['department']=department

                    info_list.append(data)
        except Exception as account_info_ERR:
            print('account_info_ERR:',str(account_info_ERR))
        finally:
            time_connect.close()

        return info_list


    """
    ***********************************************
        r_account表
        r_account表同步cgtw中的public/account表信息，获取人员的部门、等级、名称
        字段:
        id: r_account 表id
        cg_id:    cgtw中account中的id
        conf_department_id:   cgtw中conf_department_id，可以通过该字段去r_department中查询对应的部门
        conf_role_id: 暂时没用
        level:  人员等级
        artist: 人员名称
    ***********************************************
    """

    def r_account_info(self):
        """
        获取r_account表信息

        :return: 返回一个以conf_department_id为键，各人员信息[{id,cg_id,conf_department_id,conf_role_id,level,artist},..]列表为值的字典
        """
        time_connect, time_cursor = self._init_timeaxis_db()
        sql="select id,cg_id,conf_department_id,conf_role_id,level,artist from timeaxis.r_account"
        info_map={} #[data,data,...]
        try:
            time_cursor.execute(sql)
            result=time_cursor.fetchall()
            if result:
                for i in result:
                    pid,cg_id,conf_department_id,conf_role_id,level,artist=i
                    data={}
                    data['id']=pid
                    data['cg_id']=cg_id
                    data['conf_department_id']=conf_department_id
                    data['conf_role_id']=conf_role_id
                    data['level']=level
                    data['artist']=artist
                    if conf_department_id not in info_map.keys():
                        info_map[conf_department_id]=[data]
                    else:
                        info_map[conf_department_id].append(data)

        except Exception as r_account_info_ERR:
            print('r_account_info_ERR:',str(r_account_info_ERR))
        finally:
            time_connect.close()

        return info_map

    def r_account_artist_map_in_display_departments(self):
        """
        查询显示部门的所有人员信息,这些部门的人员
        :return:
        """
        department_id_name_map = {} #{department_id:xxx,...}
        account_id_artist_map = {}  #{account_id:'张三',account_id:'xxx'}
        account_id_department_map = {}
        department_id_sql = "select cg_id,name from timeaxis.r_department where display = 1"
        time_connect, time_cursor = self._init_timeaxis_db()

        try:
            time_cursor.execute(department_id_sql)
            result=time_cursor.fetchall()
            if result:

                for i in result:
                    department_id,department = i
                    department_id_name_map[department_id] = department

                connect_did = '|'.join(department_id_name_map.keys())
                account_sql = "select cg_id,artist,conf_department_id from timeaxis.r_account where conf_department_id similar to '{}'".format(connect_did)
                time_cursor.execute(account_sql)
                a_result = time_cursor.fetchall()
                if a_result:
                    for a in a_result:
                        account_id,artist,conf_department_id = a
                        account_id_artist_map[account_id] = artist
                        account_id_department_map[account_id] = department_id_name_map.get(conf_department_id,'')

        except Exception as r_account_artist_map_in_display_departments_ERR:
            print('r_account_artist_map_in_display_departments_ERR:',str(r_account_artist_map_in_display_departments_ERR))
        finally:
            time_connect.close()

        return department_id_name_map,account_id_artist_map,account_id_department_map

    def artist_level_map(self):
        """
        获取所有的人员等级映射表
        :return: {'吴子昱':'Pxx','张三':'Px'}
        """
        artist_level_map={}
        time_connect, time_cursor=self._init_timeaxis_db()
        sql="select artist,level from timeaxis.r_account"
        try:
            time_cursor.execute(sql)
            result=time_cursor.fetchall()
            if result:
                for i in result:
                    artist_level_map[i[0]]=i[1]
        except Exception as init_artist_level_map_ERR:
            print(str(init_artist_level_map_ERR))
        finally:
            time_connect.close()

        return artist_level_map

    def get_r_account_info(self):
        """
        获取r_account表信息

        :return: 返回一个以conf_department_id为键，各人员信息[{id,cg_id,conf_department_id,level,artist},..]列表为值的字典
        """
        time_connect, time_cursor = self._init_timeaxis_db()
        sql="select id,cg_id,department_id,level,artist from timeaxis.r_account"
        info_map={} #[data,data,...]
        try:
            time_cursor.execute(sql)
            result=time_cursor.fetchall()
            if result:
                for i in result:
                    pid,cg_id,conf_department_id,level,artist=i
                    data={}
                    data['id']=pid
                    data['cg_id']=cg_id
                    data['department_id']=conf_department_id
                    data['level']=level
                    data['artist']=artist
                    if conf_department_id not in info_map.keys():
                        info_map[conf_department_id]=[data]
                    else:
                        info_map[conf_department_id].append(data)

        except Exception as r_account_info_ERR:
            print('r_account_info_ERR:',str(r_account_info_ERR))
        finally:
            time_connect.close()

        return info_map

    def get_artist_level_map(self,r_account_info=None):
        artist_level_map = {}
        if not r_account_info:
            r_account_info = self.get_r_account_info()

        for v in r_account_info.values():
            for data in v:
                artist_level_map[data['artist']] = data['level']

        return artist_level_map

    def get_account_id_level_map(self,r_account_info=None):
        """
        使用account_id来表示人，而不再是人名，因为人名有重复
        :param r_account_info:
        :return:
        """
        account_id_level_map = {}
        if not r_account_info:
            r_account_info = self.get_r_account_info()

        for v in r_account_info.values():
            for data in v:
                account_id_level_map[data['cg_id']] = data['level']
        return account_id_level_map

    """
    ***********************************************
        r_department表
        r_department表同步cgtw中的public/conf_department表信息，获取部门信息
        字段:
        id:
        cg_id: cgtw表public/conf_department中的id
        name:  部门名称
        display: 是否在排期软件显示该部门
        
    ***********************************************
    """

    def r_department_info(self):
        """
        获取r_department表信息

        :return: 返回
        1.以部门名称为键,data为值的所有部门字典  {'01美术':{id,cg_id,name,display}}
        2.显示部门的信息
        3.显示部门的名称列表
        """
        time_connect, time_cursor = self._init_timeaxis_db()
        sql="select id,cg_id,name,display from timeaxis.r_department"
        info_map={}  #{'01美术':data,'02跟踪':data,...}
        display_department_map={}  #仅显示的部门的信息
        try:
            time_cursor.execute(sql)
            result=time_cursor.fetchall()
            if result:
                for i in result:
                    pid,cg_id,name,display=i
                    data={}
                    data['id']=pid
                    data['cg_id']=cg_id
                    data['name']=name
                    data['display']=display
                    info_map[name]=data
                    if display==1:
                        display_department_map[name]=data
        except Exception as r_department_info_ERR:
            print('r_department_info_ERR:',str(r_department_info_ERR))
        finally:
            time_connect.close()

        return info_map,display_department_map,list(display_department_map.keys())





    """
    ***********************************************
        r_task表
        r_task表同步cgtw中的各激活项目对应schema的task表信息，获取部门信息
        字段:
        id:
        cg_id: cgtw表task表中的id
        link_id: 对应cgtw中该项目下shot或asset表id
        set_pipeline_id: 阶段id,没用到
        flow_id: 没用到
        artist:  制作者
        module:  任务模块 shot/asset
        entity_name: 阶段名称
        status:  任务状态
        client_status_retake_count:  客户返修次数
        assigned: 是否已分配,当为0时会出现在排期软件未分配栏中
        database: 对应项目schema名称 eg :proj_xxss
        difficulty_level: 任务难度等级
        eps: 所在集数
        important_task_mark:  任务难易度
        img_path:  任务缩略图路径
        shot_name: 镜头名称
        task_name: 任务阶段名称
        last_workhour: 最后分配工时
        workhour: 工时
        department: 任务制作者所属部门
        retake_workhour: 返修工时
        last_submit_time: 最后提交时间
        task_priority: 任务优先级
        deadline: 任务最终提交日期

        r_task信息量太大,根据需要设置提取函数
    ***********************************************
    """

    def r_task_info(self):
        pass









    """
    ***********************************************
        r_asset表
        r_asset表同步cgtw中的各激活项目对应schema的asset表信息，获取资产任务信息
        字段:
        id:
        cg_id: cgtw表asset表中的id
        eps_id:  集数id
        description: 任务描述
        entity_name: 中文名称
        eps: 任务所在集数
        assign_pipeline: 该任务相关阶段
        frame:  任务帧数

        所需信息需根据具体要求来
    ***********************************************
    """
    def r_asset_info(self):
        pass




    """
    ***********************************************
        r_shot表
        r_shot表同步cgtw中的各激活项目对应schema的shot表信息，获取镜头任务信息
        字段:
        id:
        cg_id: cgtw表asset表中的id
        eps_id:  集数id
        description: 任务描述
        entity_name: 镜头号
        eps: 任务所在集数
        img_path:
        assign_pipeline: 该任务相关阶段
        frame:  任务帧数
        shot_difficulty_level: 对外报价镜头难度等级
        deadline: 镜头最终提交日期

        所需信息需根据具体要求来
    ***********************************************
    """


    def r_shot_info(self):
        pass





    """
    ***********************************************
        task表
        task表保存排期软件中所以分配的cgtw关联的任务信息
        字段:
        id:
        task_type: 任务类型
        r_id:  r_task中的id
        artist: 制作者
        position: 任务排期索引
        start_date: 起始安排日期
        end_date:   结束安排日期
        status:     任务状态
        cg_id: cgtw表task表中的id
        retake_info: 返修历史
        task_name:   cgtw任务名称
        task_pipeline:   任务阶段
        project_name:  项目名称 eg:冰糖炖雪梨(BT)
        artist_level:  制作者等级
        difficulty_level: 任务难度等级
        workhour:  工时
        module:    任务模块类型 asset/shot
        description: 任务描述
        assigned_time: 分配时间
        eps: 所在集数
        img_path: 缩略图路径
        important_task_mark:  任务难度标记
        show_name:  任务显示名称
        assigner:   分配者
        database:   任务所在schema名称 eg : proj_xxss
        note:    任务备注
        project_shortname:  任务简称 eg: XXSS
        show_workhour:  任务显示工时
        client_status_retake_count:  任务返修次数
        sort_right:  是否排序正确, 不正确的则会显示叹号图标
        assign_pipeline: 关联阶段
        department: 制作者所在部门
        project_color: 项目颜色
        retake_workhour: 返修工时
        date_length: 跨日期天数
        ignore_weekend: 是否忽略周日
        last_submit_time: 最后提交时期
        separted: 是否为拆分任务
        frame:  任务帧数  
        ingroup:  是否在任务组里
        cgworkhour: cgtw上的工时
        assigned_workhour: 已分配任务工时
        shot_name: 镜头名称
        deadline: 镜头最终提交日期

        所需信息需根据具体要求来
    ***********************************************
    """

    def task_info(self):
        pass




    def task_in_dates(self,s_start_date,s_end_date):
        """
        提取指定时间段内的任务，获取有效的所有任务，包括cgtw任务与custom任务，排除掉show_workhour为空的任务(可以为0)。需要注意的有4点：
        1.所有show_workhour为空的任务都被过滤掉，没有统计进来。
        2.所有任务都以count_day作为日期跨日期长度。假如选择的日期是2020-12-10，但是一个任务的起始日期是2020-12-09,结束日期为2020-12-12，为4天
            那么该日期就被计算为从2020-12-10到2020-12-12。count_day就为3天。
        3.所有任务的工时以count_workhour作为计算。假如2中任务的show_workhour工时为12小时，那么count_workhour = (show_workrhour/4)*3
        4.所有任务在每天的工时都以均分的方式计算。参考3

        :param s_start_date: 选择的起始日期
        :param s_end_date:   选择的结束日期
        :return: 返回为每个任务的数据列表
        [{'task_type': 'cgtw', 'artist': '李静平', 'database': 'proj_spl', 'department': '05场景',
        'show_name': 'EP08_S32_C1030-env', 'show_workhour': '2', 'date_length': 1, 'start_date': '2020-11-21',
        'end_date': '2020-11-21', 'count_day': 1, 'count_workhour': 2.0},...}
        """
        all_task_info_list=[]

        select_cgtw="select artist,database,status,department,show_name,show_workhour,start_date,end_date,date_length from timeaxis.task where project_name is not null " \
                    "and project_name !='' and show_workhour is not null and show_workhour!='' and start_date <='{}' and end_date >='{}' " \
                    " ".format(s_end_date,s_start_date) #and cast(show_workhour as float)>0


        select_custom="select custom_type,artist,database,department,show_name,show_workhour,start_date,end_date,date_length from timeaxis.custom_task where  " \
                      "show_workhour is not null and show_workhour!='' and  start_date <='{}' and end_date >='{}' " \
                    "".format(s_end_date,s_start_date)  #自定义任务 项目名称为空都算 非制作项目 #and cast(show_workhour as float)>0

        time_connect, time_cursor=self._init_timeaxis_db()
        try:
            #获取cgtw 任务
            time_cursor.execute(select_cgtw)
            result= time_cursor.fetchall()
            if result:
                print('cgtw任务有:',len(result))
                # print(result[0])
                over_task=[]
                for i in result:
                    artist,database,status,department,show_name,show_workhour,start_date,end_date,date_length=i
                    data={}
                    data['task_type']='cgtw'
                    data['artist']=artist
                    data['database']=database
                    data['status']=status
                    data['department']=department
                    data['show_name']=show_name
                    data['show_workhour']=show_workhour
                    data['date_length']=date_length
                    data['start_date']=start_date
                    data['end_date']=end_date

                    data['count_day']=date_length  #count_day用来计算切分日期后工时
                    data['cut_start_date'] =start_date  #默认截断后的起始日期为 start_date
                    data['cut_end_date']=end_date
                    s_deltel_day= self.mz_date.delta_date(s_start_date,start_date)#跨日期
                    e_deltel_day = self.mz_date.delta_date(end_date, s_end_date)  # 跨日期
                    if s_deltel_day<0:#当跨日期时，那么实际计算的日期跨度就减去
                        data['count_day']+=s_deltel_day
                        over_task.append(data)
                        data['cut_start_date']=s_start_date #
                    if e_deltel_day < 0:  # 当跨日期时，那么实际计算的日期跨度就减去
                        data['count_day'] += e_deltel_day
                        over_task.append(data)
                        data['cut_end_date'] = s_end_date  #

                    data['count_workhour']=(float(show_workhour)/date_length) *data['count_day']  #被统计进来的工时

                    all_task_info_list.append(data)
                # print('cgtw over:task', len(over_task),over_task)

            #获取自定义任务
            time_cursor.execute(select_custom)
            custom_result = time_cursor.fetchall()
            if custom_result:
                print('custom任务有:',len(custom_result))
                # print(custom_result[0])
                for c in custom_result:
                    custom_type,artist, database, department, show_name, show_workhour, start_date, end_date, date_length=c
                    c_data={}
                    c_data['task_type']='custom'
                    c_data['custom_type']=custom_type
                    c_data['artist']=artist

                    c_data['database']=database if database and database!='' else 'noproject'
                    c_data['department']=department
                    c_data['show_name']=show_name
                    c_data['show_workhour']=show_workhour
                    c_data['date_length']=date_length
                    c_data['start_date']=start_date
                    c_data['end_date']=end_date
                    c_data['count_day']=date_length  #count_day用来计算切分日期后工时
                    c_data['cut_start_date'] =start_date  #默认截断后的起始日期为 start_date
                    c_data['cut_end_date']=end_date

                    s_deltel_day= self.mz_date.delta_date(s_start_date,start_date)#跨日期
                    e_deltel_day = self.mz_date.delta_date(end_date, s_end_date)  # 跨日期
                    if s_deltel_day<0:#当跨日期时，那么实际计算的日期跨度就减去
                        c_data['count_day']+=s_deltel_day
                        over_task.append(c_data)
                        data['cut_start_date']=s_start_date #
                    if e_deltel_day < 0:  # 当跨日期时，那么实际计算的日期跨度就减去
                        c_data['count_day'] += e_deltel_day
                        over_task.append(c_data)
                        c_data['cut_end_date'] = s_end_date  #
                    c_data['count_workhour']=(float(show_workhour)/date_length) *c_data['count_day']  #被统计进来的工时

                    all_task_info_list.append(c_data)
        except Exception as task_withworkhour_in_dates_ERR:
            print('task_withworkhour_in_dates_ERR:',str(task_withworkhour_in_dates_ERR))
        finally:
            time_connect.close()

        return all_task_info_list


    def cgtw_task_in_dates(self,s_start_date,s_end_date):
        """
         获取指定日期内，所有的cgtw
        :param s_start_date:
        :param s_end_date:
        :return:
        """
        select_cgtw_task="select artist,database,status,department,show_name," \
                    "difficulty_level,show_workhour,start_date,end_date,date_length," \
                    "task_pipeline,frame,cg_id,account_id  " \
                    "from timeaxis.task where start_date <='{}' and end_date >='{}' " \
                    " ".format(s_end_date,s_start_date) #and cast(show_workhour as float)>0

        time_connect, time_cursor = self._init_timeaxis_db()
        all_tasks = []
        try:
            # 获取cgtw 任务
            time_cursor.execute(select_cgtw_task)
            result = time_cursor.fetchall()
            if result:
                print('cgtw任务有:', len(result))
                for i in result:
                    data = {}
                    data['artist'], data['database'],data['status'],data['department'],data['show_name'],\
                    data['difficulty_level'],data['show_workhour'], data['start_date'],data['end_date'],data['date_length'],\
                    data['task_pipeline'],data['frame'], data['cg_id'],data['account_id'] =i
                    all_tasks.append(data)

        except Exception as cgtw_task_in_dates_ERR:
            print(str(cgtw_task_in_dates_ERR))
        finally:
            time_connect.close()

        return all_tasks


    def cgtw_task_in_dates_by_account_id(self,account_id,s_start_date,s_end_date):
        """
        获取指定account_id，指定日期内，所有的cgtw任务
        :param account_id:
        :param s_start_date:
        :param s_end_date:
        :return:
        """
        select_cgtw_task="select artist,database,status,department,show_name," \
                    "difficulty_level,show_workhour,start_date,end_date,date_length," \
                    "task_pipeline,frame,cg_id,account_id  " \
                    "from timeaxis.task where start_date <='{}' and end_date >='{}' and account_id ='{}'" \
                    " ".format(s_end_date,s_start_date,account_id) #and cast(show_workhour as float)>0

        time_connect, time_cursor = self._init_timeaxis_db()
        all_tasks = []
        try:
            # 获取cgtw 任务
            time_cursor.execute(select_cgtw_task)
            result = time_cursor.fetchall()
            if result:
                print('cgtw任务有:', len(result))
                for i in result:
                    data = {}
                    data['artist'], data['database'],data['status'],data['department'],data['show_name'],\
                    data['difficulty_level'],data['show_workhour'], data['start_date'],data['end_date'],data['date_length'],\
                    data['task_pipeline'],data['frame'], data['cg_id'],data['account_id'] =i
                    all_tasks.append(data)

        except Exception as cgtw_task_in_dates_ERR:
            print(str(cgtw_task_in_dates_ERR))
        finally:
            time_connect.close()

        return all_tasks


    """
    ***********************************************
        public_conf表
        public_conf表记录排期软件中项目公共配置
        字段:
        id:
        week_info: 本周优先任务栏信息 (已弃用)
        project_info:  项目列表栏信息(已弃用)
        halfyear_info: 半年报项目任务信息(已弃用)
        public_info: 公告配置信息
        token: 任务的token
        pipeline: 各部门阶段名称集合


        所需信息需根据具体要求来
    ***********************************************
    """
    def public_conf_info(self):
        pass




    """
    ***********************************************
        projects表
        projects表记录排期软件中所有项目列表,本周优先,半边表栏的信息
        字段:
        id:
        row_index:     项目列表行索引
        column_index:  项目列表列索引
        project_name:  项目名称 eg: 涉过愤怒的海(FNDH)
        color:         项目颜色,排期软件中的项目颜色
        active:        项目是否激活到排期软件
        inweek:        项目是否在本周优先中显示
        week_row_index:     项目在本周优先栏的行索引
        week_column_index:  项目在本周优先栏的列索引
        inhalfyear:              项目是否在半年报栏中显示
        halfyear_row_index:      项目在半年报栏的行索引
        halfyear_column_index:   项目在半年报栏的列索引
        database:      项目对应的schema名称  eg:proj_xxss

        所需信息需根据具体要求来
    ***********************************************
    """

    def projects_info(self):
        """
        排期软件项目中的项目信息
        :return: 返回已 database为键,data为值的字典
            {'proj_xxss':data,'proj_by':data}
        """

        time_connect, time_cursor = self._init_timeaxis_db()
        sql="select id,row_index,column_index,project_name,color,active,inweek,week_row_index,week_column_index,inhalfyear,halfyear_row_index,halfyear_column_index,database from timeaxis.projects"
        info_map={}
        try:
            time_cursor.execute(sql)
            result=time_cursor.fetchall()
            if result:
                for i in result:
                    pid,row_index,column_index,project_name,color,active,inweek,week_row_index,week_column_index,inhalfyear,halfyear_row_index,halfyear_column_index,database=i
                    data={}
                    data['pid']=pid
                    data['row_index']=row_index
                    data['column_index']=column_index
                    data['project_name']=project_name
                    data['color']=color
                    data['active']=active
                    data['inweek']=inweek
                    data['week_row_index']=week_row_index
                    data['week_column_index']=week_column_index
                    data['inhalfyear']=inhalfyear
                    data['halfyear_row_index']=halfyear_row_index
                    data['halfyear_column_index']=halfyear_column_index
                    data['database']=database
                    info_map[database]=data
        except Exception as projects_info_ERR:
            print('projects_info_ERR:',str(projects_info_ERR))
        finally:
            time_connect.close()

        return info_map

    def _projects_info(self):
        """
        获取projects表中的所有激活的姓名数据,这里是通过关联后得到的项目，而不是r_project表中的所有项目，这里的项目才会被显示
        :return:{'pid':{pid:xx,row_index:xx,...},'pid2':{...},...}
        """
        projects_info={}
        time_connect, time_cursor=self._init_timeaxis_db()
        sql="select id,row_index,column_index,project_name,color,active,inweek,week_row_index,week_column_index,inhalfyear," \
            "halfyear_row_index,halfyear_column_index,database from timeaxis.projects where active = 1"
        try:
            time_cursor.execute(sql)
            result=time_cursor.fetchall()
            if result:
                for i in result:
                    pid, row_index, column_index, project_name, color, active, inweek, week_row_index, week_column_index, inhalfyear,halfyear_row_index,halfyear_column_index,database=i
                    data={}
                    data['pid']=pid
                    data['row_index']=row_index
                    data['column_index']=column_index
                    data['project_name']=project_name
                    data['color']=color
                    data['active']=active
                    data['inweek']=inweek
                    data['week_row_index']=week_row_index
                    data['week_column_index']=week_column_index
                    data['inhalfyear']=inhalfyear
                    data['halfyear_row_index']=halfyear_row_index
                    data['halfyear_column_index']=halfyear_column_index
                    data['database']=database
                    projects_info[pid]=data
        except Exception as init_projects_info_ERR:
            print('init_projects_info_ERR:',str(init_projects_info_ERR))
        finally:
            time_cursor.close()


        return projects_info


    """
    ***********************************************
        nodes表
        nodes表记录排期软件中所有添加的项目阶段信息
        字段:
        id:
        project_id: projects表中对应的id
        node_date:  节点日期
        node_info:  节点内容信息
        weight:     节点权重.当节点在同一天时,权重越大则越靠前
        update_time:节点添加\更新时间
        updater:    节点添加者

        所需信息需根据具体要求来
    ***********************************************
    """

    def nodes_info(self):
        """
        获取nodes表中的所有节点数据,以projects中的id为键，获取该项目中的所有节点
        :return:
            nodes_info :{pid:[{id:xx,project_id:xx,...}],pid:{...}],...}
            nodes_info_datekey:{'2020-12-01':[{id:xx,project_id:xx,...}],'2020-12-03':{...}],...}
        """
        nodes_info={}

        time_connect, time_cursor=self._init_timeaxis_db()
        sql="select id,project_id,node_date,node_info,weight,update_time,updater from timeaxis.nodes "
        try:
            time_cursor.execute(sql)
            result=time_cursor.fetchall()
            if result:
                for i in result:
                    nid, project_id, node_date, node_info, weight,update_time,updater=i
                    data={}
                    data['nid']=nid
                    data['project_id']=project_id
                    data['node_date']=node_date
                    data['node_info']=node_info
                    data['weight']=weight
                    data['update_time']=update_time
                    data['updater']=updater
                    if project_id not in nodes_info.keys():
                        nodes_info[project_id]=[data]
                    else:
                        nodes_info[project_id].append(data)

        except Exception as init_nodes_info_ERR:
            print('init_nodes_info_ERR:',str(init_nodes_info_ERR))
        finally:
            time_cursor.close()

        return nodes_info

    def sorted_node_info_by_date(self,nodes_info,_projects_info):
        """
        以日期为键的 节点信息,每次取之前计算一下,把项目名称与颜色拿进去,排除掉未激活的项目
        :param nodes_info:
        :param _projects_info:  {'pid':{pid:xx,row_index:xx,...},'pid2':{...},...}
        :return: nodes_info_datekey  #{'2020-12-01':[{id:xx,project_id:xx,...}],'2020-12-03':{...}],...}
        """
        nodes_info_datekey = {}  # 以日期为键的节点数据
        if nodes_info:
            for v in nodes_info.values():
                for data in v:
                    node_date=data['node_date']
                    pid=data['project_id']
                    pid_value=_projects_info.get(pid,None)   #projects_info永远只取projets表里active的项目
                    active =_projects_info[pid]['active'] if pid_value else 0   #node带有所有节点信息,所以排除掉
                    if active:  #仅读取激活的项目的nodes
                        data['color'] = _projects_info[pid]['color']
                        data['project_name']=_projects_info[pid]['project_name']
                        if node_date not in nodes_info_datekey.keys():
                            nodes_info_datekey[node_date] = [data]
                        else:
                            nodes_info_datekey[node_date].append(data)

        if nodes_info_datekey:  #在这里就根据weight进行排序，然后添加position坐标属性值，即Graph中的每人格子中的坐标
            for date,dl in nodes_info_datekey.items():
                s_list=sorted(dl,key=lambda x:x['weight'],reverse=True)  #根据weight排序,越大越靠前
                for i in range(len(s_list)):
                    s_list[i]['position'] = i   #按顺序添加position属性
                nodes_info_datekey[date] = s_list

        return nodes_info_datekey



    """
        ***********************************************
         数据读取
        ***********************************************
    """
    def get_table_colums(self,table_name,schema='timeaxis'):
        """
        获取指定表的所有列名
        :param table_name:
        :param schema:
        :return: list ['id','xxx']
        """

        sql="select column_name from information_schema.columns where table_name='{}' and table_schema='{}';".format(table_name,schema)
        time_connect, time_cursor = self._init_timeaxis_db()
        columns=[]
        try:
            time_cursor.execute(sql)
            result=time_cursor.fetchall()
            if result:
                for i in result:
                    columns.append(i[0])
        except Exception as test_get_ori_data_ERR:
            print('test_get_ori_data_ERR:',str(test_get_ori_data_ERR))


        return columns


    def get_table_data(self,table_name,schema='timeaxis'):
        """
        获取指定表的所有数据,可用于pandas直接导出
        :param table_name:
        :return: [(),(),...]
        """
        result=None
        time_connect, time_cursor = self._init_timeaxis_db()

        sql="select * from {}.{} ".format(schema,table_name)
        try:
            time_cursor.execute(sql)
            result=time_cursor.fetchall()

        except Exception as test_get_ori_data_ERR:
            print('test_get_ori_data_ERR:',str(test_get_ori_data_ERR))


        return result


    def get_table_data_by_column(self,table_name,column_names,schema='timeaxis'):
        """
        获取指定的
        :param table_name:
        :param column_names:
        :param schema:
        :return:
        """
        result=None
        time_connect, time_cursor = self._init_timeaxis_db()
        columns = ','.join(column_names)

        sql="select {} from {}.{} ".format(columns,schema,table_name)
        try:
            time_cursor.execute(sql)
            result=time_cursor.fetchall()

        except Exception as test_get_ori_data_ERR:
            print('test_get_ori_data_ERR:',str(test_get_ori_data_ERR))

        return result





    """
    ***********************************************
    ***********************************************

    测试

    ***********************************************
    ***********************************************
    """
    def get_data(self):
        data_list = []
        time_connect, time_cursor = self._init_timeaxis_db()
        databases=['proj_cl2021', 'proj_msyy', 'proj_nhl', 'proj_qgjc', 'proj_swqs', 'proj_jzsg']
        connect_database = '|'.join(databases)
        sql ="select project_name,show_name,start_date,end_date,frame,show_workhour,artist," \
             "department,note,task_type from timeaxis.task where database similar to '({})' and note !='' ".format(connect_database)
        csql = "select project_name,show_name,start_date,end_date,parent_data,show_workhour,artist," \
              "department,note,task_type from timeaxis.custom_task where database similar to '({})' ".format(
            connect_database)
        try:
            time_cursor.execute(sql)
            result = time_cursor.fetchall()
            if result:
                print(len(result))
                # for i in range(200):
                for i in result:
                    # print(result[i])
                    # print(i)
                    data_list.append(i)
            time_cursor.execute(csql)
            cresult = time_cursor.fetchall()
            if cresult:
                print(len(cresult))
                for c in cresult:
                    project_name, show_name, start_date, end_date, parent_data, \
                    show_workhour, artist,department,note,task_type = c
                    frame = parent_data.get('frame','0')
                    nc = project_name, show_name, start_date, end_date,frame,show_workhour, artist,department,note,task_type
                    data_list.append(nc)
                # for cc in range(10):
                #     print(cresult[cc])
        except Exception as get_data_ERR:
            print('get_data_ERR:',str(get_data_ERR))
        finally:
            time_connect.close()
        return data_list

